import os

import pandas as pd
from flask import Blueprint, render_template, jsonify, request, json, session, g, redirect, url_for
from models import Student, Teacher
from pymysql import cursors, IntegrityError
from collections import OrderedDict
from exts import db
from werkzeug.security import generate_password_hash, check_password_hash
from Utils.paginator import SimplePaginator

bp = Blueprint("student", __name__, url_prefix="/student")


@bp.route("/")
def student_index():
    # 所有用户都使用相同的主框架模板，菜单根据角色动态显示
    return render_template('main.html')



@bp.before_request
def before_request():
    """学生管理蓝图权限检查"""
    # 检查用户是否登录
    if session.get('is_login') != 'true':
        # 对于API请求返回JSON错误
        if request.is_json or request.path.endswith('.json') or 'list1' in request.path:
            return jsonify({'error': '请先登录'}), 401
        else:
            return redirect('/login')
    
    # 获取当前请求的路径和用户角色
    current_path = request.path
    user_role = session.get('role')
    
    # 首先检查用户角色是否有效
    if user_role not in ['teacher', 'student']:
        return jsonify({'error': f'用户角色无效: {user_role}'}), 403
    
    # 学生主页和仪表板路由，允许学生访问
    if (current_path == '/student/' or 
        current_path == '/student' or 
        current_path == '/student/dashboard' or
        current_path == '/student/dashboard/stats'):
        # 学生和教师都可以访问，但显示不同的内容
        pass
    # 学生管理功能路由（增删改查），只允许教师访问
    elif (current_path.startswith('/student/list') or 
          current_path.startswith('/student/save') or 
          current_path.startswith('/student/update') or 
          current_path.startswith('/student/delete') or
          current_path.startswith('/student/add_students_by_xsl')):
        if user_role != 'teacher':
            return jsonify({'error': '权限不足，仅教师可以管理学生信息'}), 403
    else:
        # 未定义的路由，根据角色决定访问权限
        if user_role != 'teacher':
            return jsonify({'error': '权限不足，仅教师可以访问此功能'}), 403


@bp.route("/list1", methods=["GET"])
def student_list1():
    class_id = request.args.get("class_id2")
    if class_id is None:
        students = Student.query.all()
    else:
        students = Student.query.filter_by(class_id2=class_id)
    pageIndex = request.args.get("pageIndex")
    pageSize = request.args.get("pageSize")
    # 构建响应数据，包括总数和当前页的数据
    out_data = []
    data = []
    i = 0
    for student in students:
        data.append(student.to_json())
        i += 1
    paginator = SimplePaginator(data, pageSize)
    page_data = paginator.page(pageIndex)
    out_data = page_data
    return OrderedDict(code=0, count=i, data=out_data)


@bp.route("/list")
def student_list():
    print("学生列表")
    # 1。get查找 根据主键查找
    # teacher = Teacher.query.get(1001)
    # print(teacher.name)
    # 2。fliter_by查找
    # students = Student.query.filter_by(class_id = "230101")
    return render_template('student.html')


@bp.route('/add_students_by_xsl', methods=['POST', 'GET'])
def upload():
    if request.method == 'POST':
        try:
            # 检查文件是否存在
            if 'file' not in request.files:
                return json.dumps({'code': 1, 'msg': "未选择文件"})
                
            f = request.files['file']
            if f.filename == '':
                return json.dumps({'code': 1, 'msg': "未选择文件"})
                
            # 检查文件格式
            if not f.filename.endswith(('.xlsx', '.xls')):
                return json.dumps({'code': 1, 'msg': "文件格式不正确，请上传Excel文件"})
                
            # 保存文件
            basepath = os.path.dirname(__file__)
            xsl_dir = os.path.join(basepath, 'xsl')
            if not os.path.exists(xsl_dir):
                os.makedirs(xsl_dir)
                
            upload_path = os.path.join(xsl_dir, f.filename)
            f.save(upload_path)
            
            # 读取Excel文件
            try:
                xls_file = pd.read_excel(upload_path)
            except Exception as e:
                return json.dumps({'code': 1, 'msg': f"读取Excel文件失败: {str(e)}"})
            
            # 检查必需的列是否存在
            required_columns = ["学号", "姓名", "行政班号", "教学班号"]
            missing_columns = [col for col in required_columns if col not in xls_file.columns]
            if missing_columns:
                return json.dumps({'code': 1, 'msg': f"Excel文件缺少必需的列: {', '.join(missing_columns)}"})
            
            success_count = 0
            error_count = 0
            error_messages = []
            
            # 批量插入学生
            for index, row in xls_file.iterrows():
                try:
                    student_id = row["学号"]
                    name = row["姓名"]
                    class_id1 = row["行政班号"]
                    class_id2 = row["教学班号"]
                    sex = row.get("性别", None)
                    email = row.get("邮箱", None)
                    information = row.get("其他信息", None)
                    
                    # 数据验证
                    if pd.isna(student_id) or pd.isna(name) or pd.isna(class_id1) or pd.isna(class_id2):
                        error_messages.append(f"第{index+2}行数据不完整")
                        error_count += 1
                        continue
                    
                    # 检查学号是否已存在
                    existing_student = Student.query.filter_by(ID=student_id).first()
                    if existing_student:
                        error_messages.append(f"第{index+2}行：学号{student_id}已存在")
                        error_count += 1
                        continue
                    
                    # 创建学生对象
                    student = Student(
                        ID=int(student_id), 
                        name=str(name), 
                        password=generate_password_hash(str(student_id)),
                        sex=str(sex) if not pd.isna(sex) else None,
                        email=str(email) if not pd.isna(email) else None,
                        class_id1=str(class_id1), 
                        class_id2=int(class_id2),
                        information=str(information) if not pd.isna(information) else None
                    )
                    
                    db.session.add(student)
                    success_count += 1
                    
                except Exception as e:
                    error_messages.append(f"第{index+2}行处理失败: {str(e)}")
                    error_count += 1
                    
            # 提交所有成功的记录
            if success_count > 0:
                try:
                    db.session.commit()
                except Exception as e:
                    db.session.rollback()
                    return json.dumps({'code': 1, 'msg': f"数据库提交失败: {str(e)}"})
            
            # 清理上传的文件
            try:
                os.remove(upload_path)
            except:
                pass
                
            # 返回结果
            if error_count == 0:
                return json.dumps({'code': 0, 'msg': f"导入成功，共添加{success_count}名学生"})
            elif success_count > 0:
                error_summary = "; ".join(error_messages[:5])  # 只显示前5个错误
                if len(error_messages) > 5:
                    error_summary += f"...等{len(error_messages)}个错误"
                return json.dumps({'code': 0, 'msg': f"部分导入成功：成功{success_count}个，失败{error_count}个。错误详情：{error_summary}"})
            else:
                error_summary = "; ".join(error_messages[:5])
                return json.dumps({'code': 1, 'msg': f"导入失败：{error_summary}"})
                
        except Exception as e:
            db.session.rollback()
            print(f"批量导入学生错误: {e}")
            return json.dumps({'code': 1, 'msg': f"导入失败，请联系管理员: {str(e)}"})
            
    return render_template('student.html')


@bp.route('/delete/<ids>', methods=["GET"])
def hello_delete(ids):  # put application's code here
    # 删除学生信息
    try:
        # 验证学生ID
        if not ids:
            return jsonify({'code': 1, 'msg': "学生ID不能为空"})
            
        # 查找学生
        student = Student.query.filter_by(ID=ids).first()
        if not student:
            return jsonify({'code': 1, 'msg': "学生不存在"})
        
        # 检查学生是否有关联的作业提交记录
        from models import Uploadhomework, PatrioticAnalysis
        upload_homeworks = Uploadhomework.query.filter_by(sid=ids).all()
        
        if upload_homeworks:
            # 如果有作业提交记录，需要先删除相关的分析记录
            for upload_homework in upload_homeworks:
                # 删除爱国情感分析记录
                patriotic_analyses = PatrioticAnalysis.query.filter_by(upload_homework_id=upload_homework.ID).all()
                for analysis in patriotic_analyses:
                    db.session.delete(analysis)
                
                # 删除作业提交记录
                db.session.delete(upload_homework)
        
        # 最后删除学生
        db.session.delete(student)
        db.session.commit()
        return jsonify({'code': 0, 'msg': "删除成功"})
        
    except Exception as e:
        db.session.rollback()
        print(f"删除学生错误: {e}")
        return jsonify({'code': 1, 'msg': f"删除失败: {str(e)}"})


@bp.route('/save', methods=["POST"])
def hello_save():  # put application's code here
    # 添加新学生
    try:
        data = request.json
        
        # 数据验证
        student_id = data.get("ID")
        name = data.get("name")
        sex = data.get("sex")
        email = data.get("email")
        class_id1 = data.get("class_id1")
        class_id2 = data.get("class_id2")
        
        # 必填字段验证
        if not student_id:
            return json.dumps({'code': 1, 'msg': "学号不能为空"})
        if not name:
            return json.dumps({'code': 1, 'msg': "姓名不能为空"})
        if not class_id1:
            return json.dumps({'code': 1, 'msg': "行政班号不能为空"})
        if not class_id2:
            return json.dumps({'code': 1, 'msg': "教学班号不能为空"})
            
        # 学号格式验证
        try:
            student_id = int(student_id)
        except (ValueError, TypeError):
            return json.dumps({'code': 1, 'msg': "学号必须是数字"})
            
        # 教学班号格式验证
        try:
            class_id2 = int(class_id2)
        except (ValueError, TypeError):
            return json.dumps({'code': 1, 'msg': "教学班号必须是数字"})
            
        # 检查学号是否已存在
        existing_student = Student.query.filter_by(ID=student_id).first()
        if existing_student:
            return json.dumps({'code': 1, 'msg': "学号已存在"})
            
        # 邮箱格式简单验证
        if email and '@' not in email:
            return json.dumps({'code': 1, 'msg': "邮箱格式不正确"})
            
        # 创建学生
        student = Student(
            ID=student_id, 
            password=generate_password_hash("123456"), 
            name=name,
            sex=sex,
            email=email,
            class_id1=class_id1, 
            class_id2=class_id2, 
            information=data.get("information")
        )
        
        db.session.add(student)
        db.session.commit()
        return json.dumps({'code': 0, 'msg': "添加成功"})
        
    except IntegrityError as e:
        db.session.rollback()
        return json.dumps({'code': 1, 'msg': "数据库约束错误，可能学号已存在"})
    except Exception as e:
        db.session.rollback()
        print(f"添加学生错误: {e}")
        return json.dumps({'code': 1, 'msg': "添加失败，请联系管理员"})


@bp.route('/update', methods=["POST"])
def hello_edited():  # put application's code here
    # 更新学生信息
    try:
        data = request.json
        
        # 获取学生ID
        student_id = data.get("ID")
        if not student_id:
            return json.dumps({'code': 1, 'msg': "学生ID不能为空"})
            
        # 查找学生
        student = Student.query.filter_by(ID=student_id).first()
        if not student:
            return json.dumps({'code': 1, 'msg': "学生不存在"})
        
        # 数据验证
        name = data.get("name")
        sex = data.get("sex")
        email = data.get("email")
        class_id1 = data.get("class_id1")
        class_id2 = data.get("class_id2")
        
        # 必填字段验证
        if not name:
            return json.dumps({'code': 1, 'msg': "姓名不能为空"})
        if not class_id1:
            return json.dumps({'code': 1, 'msg': "行政班号不能为空"})
        if not class_id2:
            return json.dumps({'code': 1, 'msg': "教学班号不能为空"})
            
        # 教学班号格式验证
        try:
            class_id2 = int(class_id2)
        except (ValueError, TypeError):
            return json.dumps({'code': 1, 'msg': "教学班号必须是数字"})
            
        # 邮箱格式验证
        if email and '@' not in email:
            return json.dumps({'code': 1, 'msg': "邮箱格式不正确"})
        
        # 更新学生信息
        student.name = name
        student.sex = sex
        student.email = email
        student.class_id1 = class_id1 
        student.class_id2 = class_id2
        student.information = data.get("information")
        
        db.session.commit()
        return json.dumps({'code': 0, 'msg': "修改成功"})
        
    except Exception as e:
        db.session.rollback()
        print(f"更新学生错误: {e}")
        return json.dumps({'code': 1, 'msg': "修改失败，请联系管理员"})
